{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style>.container { width:100% !important; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                                持买仓量\n",
      "date       variety 持买仓量期货公司         \n",
      "2018-10-08 CF      国投安信期货    12236.0\n",
      "           CY      国投安信期货        5.0\n",
      "           JR      国投安信期货       45.0\n",
      "           LR      国投安信期货      158.0\n",
      "           OI      国投安信期货     7619.0\n",
      "                                持卖仓量\n",
      "date       variety 持卖仓量期货公司         \n",
      "2018-10-08 CF      国投安信期货    10725.0\n",
      "           JR      国投安信期货       51.0\n",
      "           LR      国投安信期货       44.0\n",
      "           MA      国投安信期货     6928.0\n",
      "           OI      国投安信期货    42729.0\n",
      "         date variety    会员简称     持买仓量     持卖仓量      净持仓\n",
      "0  2018-10-08      CF  国投安信期货  12236.0  10725.0   1511.0\n",
      "1  2018-10-08      CY  国投安信期货      5.0      0.0      5.0\n",
      "2  2018-10-08      JR  国投安信期货     45.0     51.0     -6.0\n",
      "3  2018-10-08      LR  国投安信期货    158.0     44.0    114.0\n",
      "4  2018-10-08      OI  国投安信期货   7619.0  42729.0 -35110.0\n",
      "         date variety  contractValue\n",
      "0  2018-10-08      AG        52965.0\n",
      "1  2018-10-08      AL        72395.0\n",
      "2  2018-10-08      AP       117160.0\n",
      "3  2018-10-08      AU       269000.0\n",
      "4  2018-10-08      BU        37660.0\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>variety</th>\n",
       "      <th>AP</th>\n",
       "      <th>CF</th>\n",
       "      <th>CY</th>\n",
       "      <th>FG</th>\n",
       "      <th>JR</th>\n",
       "      <th>LR</th>\n",
       "      <th>MA</th>\n",
       "      <th>OI</th>\n",
       "      <th>PTA</th>\n",
       "      <th>RI</th>\n",
       "      <th>RM</th>\n",
       "      <th>SF</th>\n",
       "      <th>SM</th>\n",
       "      <th>SR</th>\n",
       "      <th>WH</th>\n",
       "      <th>ZC</th>\n",
       "      <th>商品板块</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th>会员简称</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-10-08</th>\n",
       "      <th>国投安信期货</th>\n",
       "      <td>0</td>\n",
       "      <td>120441810</td>\n",
       "      <td>612200</td>\n",
       "      <td>0</td>\n",
       "      <td>-350040</td>\n",
       "      <td>6085320</td>\n",
       "      <td>-229593920</td>\n",
       "      <td>-2348859000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>-734639700</td>\n",
       "      <td>-392466700</td>\n",
       "      <td>63697720</td>\n",
       "      <td>-93476500</td>\n",
       "      <td>13759120</td>\n",
       "      <td>10416700</td>\n",
       "      <td>-3584372990</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-09</th>\n",
       "      <th>国投安信期货</th>\n",
       "      <td>0</td>\n",
       "      <td>87435915</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>-707760</td>\n",
       "      <td>11547440</td>\n",
       "      <td>-230309240</td>\n",
       "      <td>-2322073600</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>-747587700</td>\n",
       "      <td>-490414480</td>\n",
       "      <td>52759210</td>\n",
       "      <td>43616820</td>\n",
       "      <td>13872600</td>\n",
       "      <td>-459548000</td>\n",
       "      <td>-4041408795</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-10</th>\n",
       "      <th>国投安信期货</th>\n",
       "      <td>0</td>\n",
       "      <td>43794125</td>\n",
       "      <td>-2213280</td>\n",
       "      <td>39462300</td>\n",
       "      <td>-590000</td>\n",
       "      <td>11852280</td>\n",
       "      <td>-246155040</td>\n",
       "      <td>-2437467760</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>-762728530</td>\n",
       "      <td>-552694940</td>\n",
       "      <td>91177560</td>\n",
       "      <td>14449680</td>\n",
       "      <td>13882880</td>\n",
       "      <td>-610689800</td>\n",
       "      <td>-4397920525</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-11</th>\n",
       "      <th>国投安信期货</th>\n",
       "      <td>0</td>\n",
       "      <td>91241040</td>\n",
       "      <td>4269125</td>\n",
       "      <td>59016320</td>\n",
       "      <td>-587000</td>\n",
       "      <td>6561160</td>\n",
       "      <td>-259144600</td>\n",
       "      <td>-2330955000</td>\n",
       "      <td>0</td>\n",
       "      <td>141960</td>\n",
       "      <td>-767200920</td>\n",
       "      <td>-649966500</td>\n",
       "      <td>64719100</td>\n",
       "      <td>3239040</td>\n",
       "      <td>13935640</td>\n",
       "      <td>-623507600</td>\n",
       "      <td>-4388238235</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-12</th>\n",
       "      <th>国投安信期货</th>\n",
       "      <td>0</td>\n",
       "      <td>43710830</td>\n",
       "      <td>1857375</td>\n",
       "      <td>74468640</td>\n",
       "      <td>-589400</td>\n",
       "      <td>3756360</td>\n",
       "      <td>-307251680</td>\n",
       "      <td>-2460921520</td>\n",
       "      <td>0</td>\n",
       "      <td>142320</td>\n",
       "      <td>-750796160</td>\n",
       "      <td>-682335530</td>\n",
       "      <td>112976920</td>\n",
       "      <td>46245500</td>\n",
       "      <td>14091000</td>\n",
       "      <td>-599898600</td>\n",
       "      <td>-4504543945</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-15</th>\n",
       "      <th>国投安信期货</th>\n",
       "      <td>0</td>\n",
       "      <td>96171565</td>\n",
       "      <td>1365155</td>\n",
       "      <td>71567400</td>\n",
       "      <td>-588000</td>\n",
       "      <td>-1411800</td>\n",
       "      <td>-320163840</td>\n",
       "      <td>-2459244480</td>\n",
       "      <td>0</td>\n",
       "      <td>284520</td>\n",
       "      <td>-789862620</td>\n",
       "      <td>-706199820</td>\n",
       "      <td>131760350</td>\n",
       "      <td>27236300</td>\n",
       "      <td>14168000</td>\n",
       "      <td>-636675000</td>\n",
       "      <td>-4571592270</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-16</th>\n",
       "      <th>国投安信期货</th>\n",
       "      <td>0</td>\n",
       "      <td>170247350</td>\n",
       "      <td>2472700</td>\n",
       "      <td>72929900</td>\n",
       "      <td>-822080</td>\n",
       "      <td>-7827100</td>\n",
       "      <td>-376721280</td>\n",
       "      <td>-2420282460</td>\n",
       "      <td>0</td>\n",
       "      <td>279960</td>\n",
       "      <td>-792354420</td>\n",
       "      <td>-659618250</td>\n",
       "      <td>77763500</td>\n",
       "      <td>-3689280</td>\n",
       "      <td>14036000</td>\n",
       "      <td>-634548800</td>\n",
       "      <td>-4558134260</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-17</th>\n",
       "      <th>国投安信期货</th>\n",
       "      <td>0</td>\n",
       "      <td>156190995</td>\n",
       "      <td>-9179700</td>\n",
       "      <td>73133040</td>\n",
       "      <td>-884400</td>\n",
       "      <td>-7454760</td>\n",
       "      <td>-262774620</td>\n",
       "      <td>-2420940480</td>\n",
       "      <td>0</td>\n",
       "      <td>283920</td>\n",
       "      <td>-767200080</td>\n",
       "      <td>-665861310</td>\n",
       "      <td>70494240</td>\n",
       "      <td>6483960</td>\n",
       "      <td>13839360</td>\n",
       "      <td>-640706400</td>\n",
       "      <td>-4454576235</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-18</th>\n",
       "      <th>国投安信期货</th>\n",
       "      <td>0</td>\n",
       "      <td>-26596295</td>\n",
       "      <td>1348930</td>\n",
       "      <td>71141280</td>\n",
       "      <td>-879300</td>\n",
       "      <td>-14585220</td>\n",
       "      <td>-19620370</td>\n",
       "      <td>-2349497640</td>\n",
       "      <td>0</td>\n",
       "      <td>281640</td>\n",
       "      <td>-753554560</td>\n",
       "      <td>-645613800</td>\n",
       "      <td>82000730</td>\n",
       "      <td>14078720</td>\n",
       "      <td>13726800</td>\n",
       "      <td>-596641500</td>\n",
       "      <td>-4224410585</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-19</th>\n",
       "      <th>国投安信期货</th>\n",
       "      <td>-301748200</td>\n",
       "      <td>-25053300</td>\n",
       "      <td>1936000</td>\n",
       "      <td>67019600</td>\n",
       "      <td>-877500</td>\n",
       "      <td>-14848800</td>\n",
       "      <td>-28448280</td>\n",
       "      <td>-2342109440</td>\n",
       "      <td>0</td>\n",
       "      <td>282120</td>\n",
       "      <td>-816219970</td>\n",
       "      <td>-657668500</td>\n",
       "      <td>98519705</td>\n",
       "      <td>25429680</td>\n",
       "      <td>14104840</td>\n",
       "      <td>-619830000</td>\n",
       "      <td>-4599512045</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-22</th>\n",
       "      <th>国投安信期货</th>\n",
       "      <td>0</td>\n",
       "      <td>-42365575</td>\n",
       "      <td>2315720</td>\n",
       "      <td>67525920</td>\n",
       "      <td>-879900</td>\n",
       "      <td>-13387400</td>\n",
       "      <td>-72365580</td>\n",
       "      <td>-2360711300</td>\n",
       "      <td>-696554970</td>\n",
       "      <td>279600</td>\n",
       "      <td>-896041440</td>\n",
       "      <td>-731767310</td>\n",
       "      <td>111569500</td>\n",
       "      <td>-54767120</td>\n",
       "      <td>14138080</td>\n",
       "      <td>-528502400</td>\n",
       "      <td>-5201514175</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-23</th>\n",
       "      <th>国投安信期货</th>\n",
       "      <td>0</td>\n",
       "      <td>104946240</td>\n",
       "      <td>3171090</td>\n",
       "      <td>71938260</td>\n",
       "      <td>-872100</td>\n",
       "      <td>-9065280</td>\n",
       "      <td>-81417490</td>\n",
       "      <td>-2295656640</td>\n",
       "      <td>-687927500</td>\n",
       "      <td>282120</td>\n",
       "      <td>-912261300</td>\n",
       "      <td>-728654000</td>\n",
       "      <td>103369035</td>\n",
       "      <td>3467920</td>\n",
       "      <td>14003000</td>\n",
       "      <td>-507655200</td>\n",
       "      <td>-4922331845</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-24</th>\n",
       "      <th>国投安信期货</th>\n",
       "      <td>0</td>\n",
       "      <td>111584500</td>\n",
       "      <td>3293595</td>\n",
       "      <td>73566480</td>\n",
       "      <td>-2920260</td>\n",
       "      <td>-14386120</td>\n",
       "      <td>-7707300</td>\n",
       "      <td>-2175067200</td>\n",
       "      <td>-653869750</td>\n",
       "      <td>271200</td>\n",
       "      <td>-914064180</td>\n",
       "      <td>-713336070</td>\n",
       "      <td>127691400</td>\n",
       "      <td>64509240</td>\n",
       "      <td>14048400</td>\n",
       "      <td>-391387500</td>\n",
       "      <td>-4477773565</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-25</th>\n",
       "      <th>国投安信期货</th>\n",
       "      <td>0</td>\n",
       "      <td>147086910</td>\n",
       "      <td>3373580</td>\n",
       "      <td>72990840</td>\n",
       "      <td>-2284000</td>\n",
       "      <td>-9979040</td>\n",
       "      <td>-129849720</td>\n",
       "      <td>-2042798700</td>\n",
       "      <td>-880229135</td>\n",
       "      <td>0</td>\n",
       "      <td>-918107610</td>\n",
       "      <td>-571662000</td>\n",
       "      <td>133492920</td>\n",
       "      <td>124528140</td>\n",
       "      <td>14614040</td>\n",
       "      <td>-201673800</td>\n",
       "      <td>-4260497575</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-26</th>\n",
       "      <th>国投安信期货</th>\n",
       "      <td>0</td>\n",
       "      <td>18003350</td>\n",
       "      <td>2633400</td>\n",
       "      <td>76944320</td>\n",
       "      <td>-2244060</td>\n",
       "      <td>1441800</td>\n",
       "      <td>-181012480</td>\n",
       "      <td>-2025210000</td>\n",
       "      <td>-884275600</td>\n",
       "      <td>0</td>\n",
       "      <td>-941181910</td>\n",
       "      <td>-600634375</td>\n",
       "      <td>156475905</td>\n",
       "      <td>192445920</td>\n",
       "      <td>14631260</td>\n",
       "      <td>-119315600</td>\n",
       "      <td>-4291298070</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-29</th>\n",
       "      <th>国投安信期货</th>\n",
       "      <td>0</td>\n",
       "      <td>118966400</td>\n",
       "      <td>-2087730</td>\n",
       "      <td>73230880</td>\n",
       "      <td>-2242500</td>\n",
       "      <td>3886560</td>\n",
       "      <td>-179391960</td>\n",
       "      <td>-1994287680</td>\n",
       "      <td>-716600830</td>\n",
       "      <td>0</td>\n",
       "      <td>-986353960</td>\n",
       "      <td>-658004600</td>\n",
       "      <td>207241880</td>\n",
       "      <td>24218880</td>\n",
       "      <td>14626200</td>\n",
       "      <td>-93367500</td>\n",
       "      <td>-4190165960</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-30</th>\n",
       "      <th>国投安信期货</th>\n",
       "      <td>0</td>\n",
       "      <td>34849600</td>\n",
       "      <td>-1282985</td>\n",
       "      <td>72320040</td>\n",
       "      <td>-2232360</td>\n",
       "      <td>216000</td>\n",
       "      <td>-192872960</td>\n",
       "      <td>-1963449450</td>\n",
       "      <td>-705275010</td>\n",
       "      <td>0</td>\n",
       "      <td>-736374830</td>\n",
       "      <td>-602476875</td>\n",
       "      <td>205781850</td>\n",
       "      <td>40156480</td>\n",
       "      <td>14540800</td>\n",
       "      <td>-97779500</td>\n",
       "      <td>-3933879200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-31</th>\n",
       "      <th>国投安信期货</th>\n",
       "      <td>0</td>\n",
       "      <td>-26245345</td>\n",
       "      <td>-1276825</td>\n",
       "      <td>-3622280</td>\n",
       "      <td>-2245620</td>\n",
       "      <td>-916640</td>\n",
       "      <td>-356361300</td>\n",
       "      <td>-1884867520</td>\n",
       "      <td>-648102600</td>\n",
       "      <td>0</td>\n",
       "      <td>-646406720</td>\n",
       "      <td>-441233650</td>\n",
       "      <td>207686160</td>\n",
       "      <td>52836000</td>\n",
       "      <td>14529440</td>\n",
       "      <td>-111982200</td>\n",
       "      <td>-3848209100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-11-01</th>\n",
       "      <th>国投安信期货</th>\n",
       "      <td>0</td>\n",
       "      <td>-30563680</td>\n",
       "      <td>0</td>\n",
       "      <td>-54067200</td>\n",
       "      <td>-2234700</td>\n",
       "      <td>-2696000</td>\n",
       "      <td>-322527360</td>\n",
       "      <td>-1892670280</td>\n",
       "      <td>-632842700</td>\n",
       "      <td>0</td>\n",
       "      <td>-680501980</td>\n",
       "      <td>-276379530</td>\n",
       "      <td>251342640</td>\n",
       "      <td>1617920</td>\n",
       "      <td>10567200</td>\n",
       "      <td>12688800</td>\n",
       "      <td>-3618266870</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-11-02</th>\n",
       "      <th>国投安信期货</th>\n",
       "      <td>0</td>\n",
       "      <td>-139890830</td>\n",
       "      <td>-1533675</td>\n",
       "      <td>-55008800</td>\n",
       "      <td>-2169800</td>\n",
       "      <td>53840</td>\n",
       "      <td>-219104500</td>\n",
       "      <td>-1849775230</td>\n",
       "      <td>-636069825</td>\n",
       "      <td>0</td>\n",
       "      <td>-637478350</td>\n",
       "      <td>-201946470</td>\n",
       "      <td>274163020</td>\n",
       "      <td>-22123200</td>\n",
       "      <td>10590300</td>\n",
       "      <td>8410500</td>\n",
       "      <td>-3471883020</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "variety                   AP         CF       CY        FG       JR        LR  \\\n",
       "date       会员简称                                                                 \n",
       "2018-10-08 国投安信期货          0  120441810   612200         0  -350040   6085320   \n",
       "2018-10-09 国投安信期货          0   87435915        0         0  -707760  11547440   \n",
       "2018-10-10 国投安信期货          0   43794125 -2213280  39462300  -590000  11852280   \n",
       "2018-10-11 国投安信期货          0   91241040  4269125  59016320  -587000   6561160   \n",
       "2018-10-12 国投安信期货          0   43710830  1857375  74468640  -589400   3756360   \n",
       "2018-10-15 国投安信期货          0   96171565  1365155  71567400  -588000  -1411800   \n",
       "2018-10-16 国投安信期货          0  170247350  2472700  72929900  -822080  -7827100   \n",
       "2018-10-17 国投安信期货          0  156190995 -9179700  73133040  -884400  -7454760   \n",
       "2018-10-18 国投安信期货          0  -26596295  1348930  71141280  -879300 -14585220   \n",
       "2018-10-19 国投安信期货 -301748200  -25053300  1936000  67019600  -877500 -14848800   \n",
       "2018-10-22 国投安信期货          0  -42365575  2315720  67525920  -879900 -13387400   \n",
       "2018-10-23 国投安信期货          0  104946240  3171090  71938260  -872100  -9065280   \n",
       "2018-10-24 国投安信期货          0  111584500  3293595  73566480 -2920260 -14386120   \n",
       "2018-10-25 国投安信期货          0  147086910  3373580  72990840 -2284000  -9979040   \n",
       "2018-10-26 国投安信期货          0   18003350  2633400  76944320 -2244060   1441800   \n",
       "2018-10-29 国投安信期货          0  118966400 -2087730  73230880 -2242500   3886560   \n",
       "2018-10-30 国投安信期货          0   34849600 -1282985  72320040 -2232360    216000   \n",
       "2018-10-31 国投安信期货          0  -26245345 -1276825  -3622280 -2245620   -916640   \n",
       "2018-11-01 国投安信期货          0  -30563680        0 -54067200 -2234700  -2696000   \n",
       "2018-11-02 国投安信期货          0 -139890830 -1533675 -55008800 -2169800     53840   \n",
       "\n",
       "variety                   MA          OI        PTA      RI         RM  \\\n",
       "date       会员简称                                                          \n",
       "2018-10-08 国投安信期货 -229593920 -2348859000          0       0 -734639700   \n",
       "2018-10-09 国投安信期货 -230309240 -2322073600          0       0 -747587700   \n",
       "2018-10-10 国投安信期货 -246155040 -2437467760          0       0 -762728530   \n",
       "2018-10-11 国投安信期货 -259144600 -2330955000          0  141960 -767200920   \n",
       "2018-10-12 国投安信期货 -307251680 -2460921520          0  142320 -750796160   \n",
       "2018-10-15 国投安信期货 -320163840 -2459244480          0  284520 -789862620   \n",
       "2018-10-16 国投安信期货 -376721280 -2420282460          0  279960 -792354420   \n",
       "2018-10-17 国投安信期货 -262774620 -2420940480          0  283920 -767200080   \n",
       "2018-10-18 国投安信期货  -19620370 -2349497640          0  281640 -753554560   \n",
       "2018-10-19 国投安信期货  -28448280 -2342109440          0  282120 -816219970   \n",
       "2018-10-22 国投安信期货  -72365580 -2360711300 -696554970  279600 -896041440   \n",
       "2018-10-23 国投安信期货  -81417490 -2295656640 -687927500  282120 -912261300   \n",
       "2018-10-24 国投安信期货   -7707300 -2175067200 -653869750  271200 -914064180   \n",
       "2018-10-25 国投安信期货 -129849720 -2042798700 -880229135       0 -918107610   \n",
       "2018-10-26 国投安信期货 -181012480 -2025210000 -884275600       0 -941181910   \n",
       "2018-10-29 国投安信期货 -179391960 -1994287680 -716600830       0 -986353960   \n",
       "2018-10-30 国投安信期货 -192872960 -1963449450 -705275010       0 -736374830   \n",
       "2018-10-31 国投安信期货 -356361300 -1884867520 -648102600       0 -646406720   \n",
       "2018-11-01 国投安信期货 -322527360 -1892670280 -632842700       0 -680501980   \n",
       "2018-11-02 国投安信期货 -219104500 -1849775230 -636069825       0 -637478350   \n",
       "\n",
       "variety                   SF         SM         SR        WH         ZC  \\\n",
       "date       会员简称                                                           \n",
       "2018-10-08 国投安信期货 -392466700   63697720  -93476500  13759120   10416700   \n",
       "2018-10-09 国投安信期货 -490414480   52759210   43616820  13872600 -459548000   \n",
       "2018-10-10 国投安信期货 -552694940   91177560   14449680  13882880 -610689800   \n",
       "2018-10-11 国投安信期货 -649966500   64719100    3239040  13935640 -623507600   \n",
       "2018-10-12 国投安信期货 -682335530  112976920   46245500  14091000 -599898600   \n",
       "2018-10-15 国投安信期货 -706199820  131760350   27236300  14168000 -636675000   \n",
       "2018-10-16 国投安信期货 -659618250   77763500   -3689280  14036000 -634548800   \n",
       "2018-10-17 国投安信期货 -665861310   70494240    6483960  13839360 -640706400   \n",
       "2018-10-18 国投安信期货 -645613800   82000730   14078720  13726800 -596641500   \n",
       "2018-10-19 国投安信期货 -657668500   98519705   25429680  14104840 -619830000   \n",
       "2018-10-22 国投安信期货 -731767310  111569500  -54767120  14138080 -528502400   \n",
       "2018-10-23 国投安信期货 -728654000  103369035    3467920  14003000 -507655200   \n",
       "2018-10-24 国投安信期货 -713336070  127691400   64509240  14048400 -391387500   \n",
       "2018-10-25 国投安信期货 -571662000  133492920  124528140  14614040 -201673800   \n",
       "2018-10-26 国投安信期货 -600634375  156475905  192445920  14631260 -119315600   \n",
       "2018-10-29 国投安信期货 -658004600  207241880   24218880  14626200  -93367500   \n",
       "2018-10-30 国投安信期货 -602476875  205781850   40156480  14540800  -97779500   \n",
       "2018-10-31 国投安信期货 -441233650  207686160   52836000  14529440 -111982200   \n",
       "2018-11-01 国投安信期货 -276379530  251342640    1617920  10567200   12688800   \n",
       "2018-11-02 国投安信期货 -201946470  274163020  -22123200  10590300    8410500   \n",
       "\n",
       "variety                  商品板块  \n",
       "date       会员简称                \n",
       "2018-10-08 国投安信期货 -3584372990  \n",
       "2018-10-09 国投安信期货 -4041408795  \n",
       "2018-10-10 国投安信期货 -4397920525  \n",
       "2018-10-11 国投安信期货 -4388238235  \n",
       "2018-10-12 国投安信期货 -4504543945  \n",
       "2018-10-15 国投安信期货 -4571592270  \n",
       "2018-10-16 国投安信期货 -4558134260  \n",
       "2018-10-17 国投安信期货 -4454576235  \n",
       "2018-10-18 国投安信期货 -4224410585  \n",
       "2018-10-19 国投安信期货 -4599512045  \n",
       "2018-10-22 国投安信期货 -5201514175  \n",
       "2018-10-23 国投安信期货 -4922331845  \n",
       "2018-10-24 国投安信期货 -4477773565  \n",
       "2018-10-25 国投安信期货 -4260497575  \n",
       "2018-10-26 国投安信期货 -4291298070  \n",
       "2018-10-29 国投安信期货 -4190165960  \n",
       "2018-10-30 国投安信期货 -3933879200  \n",
       "2018-10-31 国投安信期货 -3848209100  \n",
       "2018-11-01 国投安信期货 -3618266870  \n",
       "2018-11-02 国投安信期货 -3471883020  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pymongo\n",
    "import pandas as pd\n",
    "import matplotlib as plt\n",
    "from pandas import Series,DataFrame\n",
    "pd.set_option('display.width', None)  # 设置字符显示宽度\n",
    "pd.set_option('display.max_rows', None)  # 设置显示最大行\n",
    "pd.set_option('display.max_columns', None)  # 设置显示最大行\n",
    "#二行即可搞定画图中文乱码\n",
    "plt.rcParams['font.sans-serif']=['SimHei'] #用来正常显示中文标签\n",
    "plt.rcParams['axes.unicode_minus']=False #用来正常显示负号\n",
    "from IPython.core.display import display, HTML\n",
    "display(HTML(\"<style>.container { width:100% !important; }</style>\"))\n",
    "\n",
    "#连接数据库\n",
    "client = pymongo.MongoClient('localhost',27017)\n",
    "futures = client.futures\n",
    "\n",
    "market = futures.market\n",
    "unit = futures.unit\n",
    "position = futures.position\n",
    "\n",
    "#加载数据\n",
    "market = DataFrame(list(market.find()))\n",
    "unit = DataFrame(list(unit.find()))\n",
    "position = DataFrame(list(position.find()))\n",
    "\n",
    "#大写字母\n",
    "position['variety']=position['variety'].str.upper()\n",
    "\n",
    "#删除/选取某行含有特殊数值的列\n",
    "position=position.set_index('名次')\n",
    "\n",
    "#选择需要显示的字段\n",
    "data1=market[['date','variety','set_close']]\n",
    "\n",
    "data2=unit[[ 'variety','unit']]\n",
    "position=position[['date','variety','symbol','持买仓量期货公司','持买仓量', '持买仓量增减','持卖仓量期货公司','持卖仓量', '持卖仓量增减']]\n",
    "\n",
    "#查询会员\n",
    "members='国投安信期货'\n",
    "data3=position[(position['持买仓量期货公司'] == members)]\n",
    "#汇总合约\n",
    "data3=data3[['date','variety','持买仓量期货公司','持买仓量']]\n",
    "data3=data3.groupby(['date','variety','持买仓量期货公司'])[['持买仓量']].sum()\n",
    "data4=position[(position['持卖仓量期货公司'] == members)]\n",
    "data4=data4[['date','variety','持卖仓量期货公司','持卖仓量']]\n",
    "data4=data4.groupby(['date','variety','持卖仓量期货公司'])[['持卖仓量']].sum()\n",
    "print(data3.head())\n",
    "print(data4.head())\n",
    "\n",
    "#并集\n",
    "data5=pd.merge(data3,data4, on=['date','variety'],how='outer')\n",
    "data5['会员简称']=data5.apply(lambda x: members,axis=1)\n",
    "\n",
    "#nan缺失值填充fillna()为0\n",
    "data5=data5.fillna(0)\n",
    "data5['净持仓']=data5.apply(lambda x: x['持买仓量']-x['持卖仓量'],axis=1)\n",
    "#选择需要显示的字段\n",
    "data5=data5[['会员简称','持买仓量','持卖仓量','净持仓']]\n",
    "data5=data5.reset_index(['variety','date'])\n",
    "print(data5.head())\n",
    "netpostion=data5.set_index('date')\n",
    "\n",
    "netpostion=data5.pivot_table('净持仓',index='date',columns='variety',fill_value=0)\n",
    "\n",
    "#合约价值\n",
    "contractValue=pd.merge(data1,data2,how='left',sort=False).drop_duplicates()\n",
    "contractValue['contractValue'] = contractValue.apply(lambda x: x['set_close']*x['unit'],axis=1)\n",
    "contractValue=contractValue[['date','variety','contractValue']]\n",
    "#值替换replace()\n",
    "contractValue=contractValue.replace(['TA'],'PTA')\n",
    "print(contractValue.head())\n",
    "sz=pd.merge(data5,contractValue,on=['date','variety'],how='left')\n",
    "\n",
    "#净持仓价值\n",
    "sz['净持仓价值']=sz.apply(lambda x: x['净持仓']*x['contractValue'],axis=1)\n",
    "sz=sz[['date','variety','会员简称','净持仓价值']]\n",
    "sz=sz.sort_values(by='净持仓价值')\n",
    "sz=sz.pivot_table('净持仓价值',index=['date','会员简称'],columns='variety',fill_value=0)\n",
    "sz=sz.copy()\n",
    "sz['商品板块']=sz.sum(axis=1)\n",
    "sz\n",
    "\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
